﻿using System;
using System.Collections.Generic;
using System.Web;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;
using System.Security.Cryptography;
using System.Net;
using System.IO;

namespace WebApplication1
{
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data;
    public class Data
    {
        public string Get_DateTime()
        {
            DataBase db = new DataBase();




            DateTime Daytemp = DateTime.Now;
            string year = Daytemp.Year.ToString();
            string month = null;
            string day = null;
            month = Daytemp.Month.ToString();
            day = Daytemp.Day.ToString();
            if (Daytemp.Month.ToString().Length == 1)
                month = "0" + Daytemp.Month.ToString();

            if (Daytemp.Day.ToString().Length == 1)
                day = "0" + Daytemp.Day.ToString();


            return year +"/"+ month+"/" + day + " " + DateTime.Now.ToLongTimeString();


        }

        public DataSet EQInfo_By_ID(string ID)
        {

            DataBase db = new DataBase();

            string EQFlag = ID;

                DataSet ds = new DataSet();

                string selectStr2 = "select 设备表.设备名称,设备表.设备型号,设备表.出厂编号,设备表.生产厂商,设备表.购入日期,Depts.Name,Depts.DeptNumber,设备表.Flag,设备表.存放位置,Dept2.Name FROM 设备表 JOIN Depts ON  convert(nvarchar(255),设备表.所属科室)= convert(nvarchar(255),Depts.DeptNumber) JOIN Depts Dept2 ON  convert(nvarchar(255),Depts.PID)= convert(nvarchar(255),Dept2.DeptNumber) where convert(nvarchar(255),Flag)= '" + EQFlag + "'"; ;

                ds = db.RunProcReturnDS(selectStr2);

            return ds;




        }

        public DataSet DEL_Log_By_Flag(string Flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "delete from 报修任务记录表 where Flag='" + Flag + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet DEL_Task_By_Flag(string Flag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            string selectStr = "delete from 报修任务表 where Flag='" + Flag + "'";

            ds = db.RunProcReturnDS(selectStr);
            return ds;


        }
        public DataSet TaskInfo(string Flag)
        {

            DataBase db = new DataBase();

            

            DataSet ds = new DataSet();

            string selectStr2 = "select 设备表.设备名称,设备表.设备型号,设备表.出厂编号,设备表.生产厂商,设备表.购入日期,Depts.Name,Depts.DeptNumber,设备表.Flag,设备表.存放位置,Dept2.Name,报修任务表.报修人,报修任务表.报修人电话,报修任务表.报修时间,报修任务表.故障描述,报修任务表.维修进程 FROM 报修任务表  JOIN 设备表 ON  convert(nvarchar(255),报修任务表.设备Flag)= convert(nvarchar(255),设备表.Flag) JOIN Depts ON  convert(nvarchar(255),设备表.所属科室)= convert(nvarchar(255),Depts.DeptNumber) JOIN Depts Dept2 ON  convert(nvarchar(255),Depts.PID)= convert(nvarchar(255),Dept2.DeptNumber) where convert(nvarchar(255),报修任务表.Flag)= '" + Flag + "'"; ;

            ds = db.RunProcReturnDS(selectStr2);

            return ds;




        }
        public DataSet LogInfo(string Flag)
        {

            DataBase db = new DataBase();



            DataSet ds = new DataSet();

            string selectStr2 = "select * FROM 报修任务记录表 where convert(nvarchar(255),报修任务记录表.Flag)= '" + Flag + "'"; ;

            ds = db.RunProcReturnDS(selectStr2);

            return ds;




        }

     
        public DataSet Task_Image_List(string Flag)
        {

            DataBase db = new DataBase();



            DataSet ds = new DataSet();

            string selectStr2 = "select * FROM  报修任务文件表 where convert(nvarchar(255),报修任务文件表.Flag)= '" + Flag + "'"; ;

            ds = db.RunProcReturnDS(selectStr2);

            return ds;




        }

        public DataSet Log_Image_List(string Flag)
        {

            DataBase db = new DataBase();



            DataSet ds = new DataSet();

            string selectStr2 = "select * FROM  报修任务记录文件表 where convert(nvarchar(255),报修任务记录文件表.Flag)= '" + Flag + "'"; ;

            ds = db.RunProcReturnDS(selectStr2);

            return ds;




        }
        public string Add_Approve(string Flag, string Approve, string Approvecontent, string ApproveUserID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();

            db.RunProc("insert into 审批表 (任务Flag,审批意见,审批附言,审批人,审批时间) values('" + Flag + "','" + Approve + "','" + Approvecontent + "','" + ApproveUserID + "','" + Get_DateTime() + "')").ToString();


            return db.RunProc("update 报修任务表 set 审批意见 ='" + Approve + "',审批附言='" + Approvecontent + "', 审批人='" + ApproveUserID + "' where  Flag= '" + Flag + "'").ToString();



        }

        public string Add_Apply(string Flag, string ApplyContent, double facprice, double applyprice, string UserID)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();



            return db.RunProc("update 报修任务表 set 审批意见 ='等待审批',市场价格='" + facprice + "', 维修预算='" + applyprice + "',预算附言='" + ApplyContent + "', 申请人='" + UserID + "'  where  Flag= '" + Flag + "'").ToString();



        }
        public string Add_Task_Log(string TaskFlag, string Content, string UserName, string AddTim)
        {
            string Flag = Guid.NewGuid().ToString();
            DataBase db = new DataBase();
            int result = db.RunProc("insert into 报修任务记录表(Flag,Task_Flag,记录内容,记录人,记录时间) values('" + Flag + "','" + TaskFlag + "','" + Content + "','" + UserName + "','" + AddTim + "')");
            return result.ToString();

        }
        public DataSet Task_Approved_List(string TaskFlag)
        {
            DataBase db = new DataBase();

            DataSet ds = new DataSet();





            string selectStr = "select 审批表.ID,审批表.审批意见,审批表.审批附言,审批表.审批时间,审批表.审批人 FROM  审批表   WHERE convert(nvarchar(255),审批表.任务Flag)= '" + TaskFlag + "'  order by 审批表.ID";

            ds = db.RunProcReturnDS(selectStr);


            return ds;


        }
        public DataSet Task_Log_List(string Flag)
        {

            DataBase db = new DataBase();



            DataSet ds = new DataSet();

            string selectStr2 = "select * FROM  报修任务记录表 where convert(nvarchar(255),报修任务记录表.Task_Flag)= '" + Flag + "'"; ;

            ds = db.RunProcReturnDS(selectStr2);

            return ds;




        }
        public DataSet Temp_EQ_Info_By_ID(string ID)
        {

            DataBase db = new DataBase();

            DataSet ds1 = new DataSet();
            string selectStr = "select * FROM 编号报修表 where convert(nvarchar(255),RPNr)= '" + ID + "'"; 

            ds1 = db.RunProcReturnDS(selectStr);
   
            return ds1;




        }

        public string Task_Wait_Repply_Count()
        {

            DataBase db = new DataBase();

            DataSet ds1 = new DataSet();
            string selectStr = "select ID FROM 报修任务表 where convert(nvarchar(255),维修进程)= '等待回应'";

            ds1 = db.RunProcReturnDS(selectStr);

            return ds1.Tables[0].Rows.Count.ToString();




        }
        public string EQ_Count()
        {

            DataBase db = new DataBase();

            DataSet ds1 = new DataSet();
            string selectStr = "select ID FROM 设备表 "; ;

            ds1 = db.RunProcReturnDS(selectStr);

            return ds1.Tables[0].Rows.Count.ToString();




        }
        public string Task_Count()
        {

            DataBase db = new DataBase();

            DataSet ds1 = new DataSet();
            string selectStr = "select ID FROM 报修任务表 where convert(nvarchar(255),维修进程)!= '任务完成 '"; 

            ds1 = db.RunProcReturnDS(selectStr);

            return ds1.Tables[0].Rows.Count.ToString();




        }
        public string Task_Cost()
        {

            DataBase db = new DataBase();


            string selectStr = "select  sum(维修价格) FROM 报修任务表 "; 

            DataSet ds1 = db.RunProcReturnDS(selectStr);

            return ds1.Tables[0].Rows[0][0].ToString();

          


        }
        public string Task_Cost_Save()
        {

            DataBase db = new DataBase();


            string selectStr = "select  sum(维修价格) FROM 报修任务表 ";
            string selectStr2 = "select  sum(维修预算) FROM 报修任务表 ";

            DataSet ds1 = db.RunProcReturnDS(selectStr);
            DataSet ds2 = db.RunProcReturnDS(selectStr2);
            float sd=float.Parse( ds2.Tables[0].Rows[0][0].ToString())-float.Parse( ds1.Tables[0].Rows[0][0].ToString());
            return sd.ToString();




        }

        public string Task_Count_All()
        {

            DataBase db = new DataBase();

            DataSet ds1 = new DataSet();
            string selectStr = "select ID FROM 报修任务表 ";

            ds1 = db.RunProcReturnDS(selectStr);

            return ds1.Tables[0].Rows.Count.ToString();




        }


        public string Add_Task(string Flag, string EQFlag, string Describe, string FaultCode, string CallPerson, string CallTime, string AnswerTime, string TaskPersonID, string Progress, string PictureCount, string VoiceCount, string VideoCount, string Department, string Hospital, string Type, string Repair_Cost,string CallPhone,string userid)
        {
           
            DataBase db = new DataBase();

            int i = db.RunProc("insert into 报修任务表 (Flag,设备Flag,故障描述,故障代码,报修人,报修时间,回应时间,执行人,维修进程,图片数量,音频数量,视频数量,所属科室,所属医院,任务类别,维修费,生成日期,报修人电话,报修人微信UserID) values('" + Flag + "','" + EQFlag + "','" + Describe + "','" + FaultCode + "','" + CallPerson + "','" + CallTime + "','" + AnswerTime + "','" + TaskPersonID + "','" + Progress + "','" + PictureCount + "','" + VoiceCount + "','" + VideoCount + "','" + Department + "','" + Hospital + "','" + Type + "','" + Repair_Cost + "','" + Get_DateTime() + "','" + CallPhone + "','" + userid + "')");

            return i.ToString();
        }

        public string SendSMS(string phonenr, string deptname, string eqname)
        {
            Util.SetCertificatePolicy();
            String url = "https://api.netease.im/sms/sendtemplate.action";
            url += "?templateid=7408&mobiles=['" + phonenr + "']&params=['" + deptname + "','" + eqname + "']";//TO开发者：accid填要创建的id

            String appKey = "5d1fd1568f14f138a75ce1ead82f47b3";//TO开发者：假数据，具体参考开发文档
            String appSecret = "122d5e94051d";//TO开发者：假数据，具体参考开发文档
            String nonce = "12345";//TO开发者：具体参考开发文档

            TimeSpan ts = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1);
            Int32 ticks = System.Convert.ToInt32(ts.TotalSeconds);
            String curTime = ticks.ToString();
            String checkSum = CheckSumBuilder.getCheckSum(appSecret, nonce, curTime);

            IDictionary<object, String> headers = new Dictionary<object, String>();
            headers["AppKey"] = appKey;
            headers["Nonce"] = nonce;
            headers["CurTime"] = curTime;
            headers["CheckSum"] = checkSum;
            headers["ContentType"] = "application/x-www-form-urlencoded;charset=utf-8";
            return HttpClient.HttpPost(url, null, headers);
        }


        class CheckSumBuilder
        {
            // 计算并获取CheckSum
            public static String getCheckSum(String appSecret, String nonce, String curTime)
            {
                byte[] data = Encoding.Default.GetBytes(appSecret + nonce + curTime);
                byte[] result;

                SHA1 sha = new SHA1CryptoServiceProvider();
                // This is one implementation of the abstract class SHA1.
                result = sha.ComputeHash(data);

                return getFormattedText(result);
            }

            // 计算并获取md5值
            public static String getMD5(String requestBody)
            {
                if (requestBody == null)
                    return null;

                // Create a new instance of the MD5CryptoServiceProvider object.
                MD5 md5Hasher = MD5.Create();

                // Convert the input string to a byte array and compute the hash.
                byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(requestBody));

                // Create a new Stringbuilder to collect the bytes
                // and create a string.
                StringBuilder sBuilder = new StringBuilder();

                // Loop through each byte of the hashed data 
                // and format each one as a hexadecimal string.
                for (int i = 0; i < data.Length; i++)
                {
                    sBuilder.Append(data[i].ToString("x2"));
                }

                // Return the hexadecimal string.
                return getFormattedText(Encoding.Default.GetBytes(sBuilder.ToString()));
            }

            private static String getFormattedText(byte[] bytes)
            {
                char[] HEX_DIGITS = { '0', '1', '2', '3', '4', '5',
            '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' };
                int len = bytes.Length;
                StringBuilder buf = new StringBuilder(len * 2);
                for (int j = 0; j < len; j++)
                {
                    buf.Append(HEX_DIGITS[(bytes[j] >> 4) & 0x0f]);
                    buf.Append(HEX_DIGITS[bytes[j] & 0x0f]);
                }
                return buf.ToString();
            }
        }

        class HttpClient
        {
            public static string HttpPost(string url, Stream data, IDictionary<object, string> headers)
            {
                System.Net.WebRequest request = HttpWebRequest.Create(url);
                request.Method = "POST";
                if (data != null)
                    request.ContentLength = data.Length;
                request.ContentType = "application/x-www-form-urlencoded;charset=utf-8";

                if (headers != null)
                {
                    foreach (var v in headers)
                    {
                        if (v.Key is HttpRequestHeader)
                            request.Headers[(HttpRequestHeader)v.Key] = v.Value;
                        else
                            request.Headers[v.Key.ToString()] = v.Value;
                    }
                }
                HttpWebResponse response = null;
                try
                {
                    // Get the response.
                    response = (HttpWebResponse)request.GetResponse();
                    // Display the status.
                    // Console.WriteLine(response.StatusDescription);
                    // Get the stream containing content returned by the server.
                    Stream dataStream = response.GetResponseStream();
                    // Open the stream using a StreamReader for easy access.
                    StreamReader reader = new StreamReader(dataStream, System.Text.Encoding.Default);
                    // Read the content.
                    string responseFromServer = reader.ReadToEnd();
                    // Display the content.
                    return responseFromServer;


                }
                catch (Exception e)
                {
                    // Console.WriteLine(e.Message);
                    //Console.WriteLine(response.StatusDescription);
                    return e.Message;
                }



            }
        }
    }
}